package com.utils.sqlite;

import org.sqlite.core.DB;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
 *
 * 本类基于 sqlite jdbc v56
 *
 * @author haoqipeng
 */
public class SqliteHelper {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;

    /**
     * 构造函数
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public SqliteHelper() {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public boolean connectTo(String dbFilePath){
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
            statement = connection.createStatement();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }


    /**
     * 执行sql查询
     * @param sql sql select 语句
     * @param rse 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     *//*
    public <T> T executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {
        try {
            resultSet = getStatement().executeQuery(sql);
            T rs = rse.extractData(resultSet);
            return rs;
        } finally {
            destroyed();
        }
    }*/

    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param rm 结果集的行数据处理类对象
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     *//*
    public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
        List<T> rsList = new ArrayList<T>();
        try {
            resultSet = getStatement().executeQuery(sql);
            while (resultSet.next()) {
                rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
            }
        } finally {
            destroyed();
        }
        return rsList;
    }*/

    /**
     * 执行数据库更新sql语句
     * @param sql
     * @return 更新行数
     * @throws SQLException
     */
    public int executeUpdate(String sql) {
        try {
            int c = statement.executeUpdate(sql);
            return c;
        } catch (SQLException e) {
            e.printStackTrace();
            destroyed();
        }
        return 0;
    }

    /**
     * 执行多个sql更新语句
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String...sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                int result = statement.executeUpdate(sql);
                if ( result == Statement.EXECUTE_FAILED ){
                    break;
                }
            }
        } finally {
            destroyed();
        }
    }

    /**
     * 执行数据库更新 sql List
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                int result = statement.executeUpdate(sql);
                if ( result == Statement.EXECUTE_FAILED ){
                    break;
                }
            }
        } finally {
            destroyed();
        }
    }


    /**
     * 数据库资源关闭和释放
     */
    public void destroyed() {
        try {

            if ( statement != null ){
                statement.close();
                statement = null;
            }

            if (null != connection) {
                connection.close();
                connection = null;
            }


            if (null != resultSet) {
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException e) {
            //System.out.println("Sqlite数据库关闭时异常");
        }
    }
}